import allure, pymysql

dbinfo = {
    "host": "rm-2zev9dj4ky5d09pf98o.mysql.rds.aliyuncs.com",
    "user": "zhoukangkang",
    "password": "L89EV8a$saRJ5b@",
    "port": 3306
}

test_dbinfo = {
    "host": "192.168.1.206",
    "user": "root",
    "password": "yodo1pwd",
    "port": 3306
}

class DbConnect():
    @allure.step("建立数据库连接")
    def __init__(self, db_cof, database="olconfig"):
        """打开数据库连接"""
        self.db_cof = db_cof
        self.db = pymysql.connect(database=database, cursorclass=pymysql.cursors.DictCursor, **db_cof)
        self.cursor = self.db.cursor()

    @allure.step("查询sql语句，所有数据")
    def select_all(self, sql):
        """sql查询s所有数据"""
        self.cursor.execute(sql)
        results = self.cursor.fetchall()    #查询所有的数据
        return results

    @allure.step("查询sql语句，最后一条数据")
    def select_lastOne(self, sql):
        """sql查询最后一条数据"""
        self.cursor.execute(sql)
        results = self.cursor.fetchone()    #查询最后一条数据
        return results

    @allure.step("执行sql语句")
    def execut(self, sql):
        """执行sql语句（增，删，改）"""
        try:
            """尝试执行"""
            self.cursor.execute(sql)
            self.db.commit()    #提交动作
        except:
            """发生错误时回滚"""
            self.db.rollback()

    @allure.step("关闭数据库连接")
    def close(self):
        self.db.close()


def select_sql_all(sql, db_cof=dbinfo, dbname="olconfig"):
    """sql查询_所有数据"""
    db = DbConnect(db_cof, dbname)
    res = db.select_all(sql)
    db.close()
    return res

def select_sql_lastOne(sql, db_cof=dbinfo, dbname="olconfig"):
    """sql查询_最后一条数据"""
    db = DbConnect(db_cof, dbname)
    res = db.select_lastOne(sql)
    db.close
    return res

def exectue_sql(sql, db_cof=dbinfo, dbname="olconfig"):
    """执行sql语句"""
    db = DbConnect(db_cof, dbname)
    db.execut(sql)
    db.close



if __name__ == '__main__':
    sql = "select * from anti.anti_certification ORDER BY id DESC"
    # sql1 = "DELETE from anti_addiction_template where name='中国网游防沉迷自动化模板名称1595494061'"
    result = select_sql_all(sql, dbname="anti")
    print(result[1]["yid"])